SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sptblMatchPlayers_AddTeamSel' AND ROUTINE_TYPE = 'PROCEDURE')
	DROP PROCEDURE sptblMatchPlayers_AddTeamSel
GO
CREATE PROCEDURE sptblMatchPlayers_AddTeamSel
	@mat_HomeClubname varchar(20),
	@mat_HomeSeason VARCHAR(20),
	@mat_HomeTeamName varchar(20),	
	@mat_AgainstClubname varchar(20),
	@mat_AgainstSeason VARCHAR(20),
	@mat_AgainstTeamName varchar(20),
	@mat_Date date,
	@UFGUsername varchar(50)
AS
BEGIN
	DECLARE @TranCount int
	SET @TranCount = @@TRANCOUNT 
	IF @TranCount > 0
		SAVE TRAN sptblMatchPlayers_AddTeamSel
	ELSE
		BEGIN TRAN
			
	DECLARE @Clubname varchar(20)	
	DECLARE @TeamName varchar(20)		
	DECLARE @Season varchar(20)		
	
	SET NOCOUNT ON;
	
    BEGIN TRY
    
    IF (dbo.fnGetClubnameByUser(@UFGUsername) = @mat_HomeClubname)
	BEGIN
		SET @Clubname = @mat_HomeClubname
		SET @Season = @mat_HomeSeason
		SET @TeamName = @mat_HomeTeamName
	END
	ELSE IF (dbo.fnGetClubnameByUser(@UFGUsername) = @mat_AgainstClubname)
	BEGIN
		SET @Clubname = @mat_AgainstClubname
		SET @Season = @mat_AgainstSeason
		SET @TeamName = @mat_AgainstTeamName
	END
	ELSE
	BEGIN
		RAISERROR ('UserNotAuthorised', 16, 1)
	END
        
    INSERT INTO dbo.tblMatchPlayers
		(mpl_HomeClubname,mpl_HomeSeason,mpl_HomeTeamName,mpl_AgainstClubname,mpl_AgainstSeason,mpl_AgainstTeamName,mpl_Clubname,mpl_Season,mpl_TeamName,mpl_PersonID)	
	--Alle spelers van het betreffende uit of thuis team
	(SELECT @mat_HomeClubname,
		@mat_HomeSeason,
		@mat_HomeTeamName,
		@mat_AgainstClubname,
		@mat_AgainstSeason,
		@mat_AgainstTeamName,
		tmm_Clubname,
		tmm_Season,
		tmm_TeamName,
		tmm_PersonID FROM dbo.tblTeamMembers
		WHERE tmm_Season = @Season 			
			-- Spelers uit het uit of thuis team
			AND tmm_Clubname+tmm_TeamName IN (@mat_HomeClubname+@mat_HomeTeamName, @mat_AgainstClubname+@mat_AgainstTeamName)
			-- Behalve spelers reeds geselecteerd
			AND tmm_PersonID NOT IN 				
				(SELECT mpl_PersonID FROM dbo.tblMatchPlayers 
				 WHERE mpl_HomeClubname = @mat_HomeClubname
					AND mpl_HomeSeason = @mat_HomeSeason
					AND mpl_HomeTeamName = @mat_HomeTeamName
					AND mpl_AgainstClubname = @mat_AgainstClubname
					AND mpl_AgainstSeason = @mat_AgainstSeason
					AND mpl_AgainstTeamName = @mat_AgainstTeamName)
			-- En alleen actieve spelers
			AND tmm_PersonID IN			
				(SELECT prs_ID FROM dbo.tblPersons
				 WHERE prs_Active = 1 AND prs_State = 'Speler')
			-- Behalve spelers die scheidsrechter zijn
			AND tmm_PersonID NOT IN
				(SELECT mrf_PersonID FROM dbo.tblMatchReferees
				 WHERE mrf_HomeClubname = @mat_HomeClubname
					AND mrf_HomeSeason = @mat_HomeSeason
					AND mrf_HomeTeamName = @mat_HomeTeamName
					AND mrf_HomeClubname = @mat_AgainstClubname
					AND mrf_AgainstSeason = @mat_AgainstSeason
					AND mrf_AgainstTeamName = @mat_AgainstTeamName)
			AND dbo.fnGetSuspensions(tmm_PersonID,@mat_Date,@Season) = 0)
	
	IF @TranCount = 0
		COMMIT TRAN	
		
	END TRY
	BEGIN CATCH
	    DECLARE @ErrorMessage NVARCHAR(4000);
	    DECLARE @ErrorSeverity INT;
	    DECLARE @ErrorState INT;
	    SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();
	    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);	    
	    
	    IF @TranCount > 0
			ROLLBACK TRAN sptblMatchPlayers_AddTeamSel
		ELSE
			ROLLBACK TRAN
	END CATCH
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sptblMatchPlayers_AddTeamSelAct' AND ROUTINE_TYPE = 'PROCEDURE')
	DROP PROCEDURE sptblMatchPlayers_AddTeamSelAct
GO
CREATE PROCEDURE sptblMatchPlayers_AddTeamSelAct
	@mat_HomeClubname varchar(20),
	@mat_HomeSeason VARCHAR(20),
	@mat_HomeTeamName varchar(20),	
	@mat_AgainstClubname varchar(20),
	@mat_AgainstSeason VARCHAR(20),
	@mat_AgainstTeamName varchar(20),
	@mat_Date date,
	@UFGUsername varchar(50)
AS
BEGIN
	DECLARE @Clubname varchar(20)	
	DECLARE @Season VARCHAR(20)
	DECLARE @TeamName varchar(20)
	SET NOCOUNT ON;
	
	SET @Clubname = dbo.fnGetClubnameByUser(@UFGUsername)

    BEGIN TRY
    
    IF (@Clubname = @mat_HomeClubname)
	BEGIN
		SET @Clubname = @mat_HomeClubname
		SET @Season = @mat_HomeSeason
		SET @TeamName = @mat_HomeTeamName
	END
	ELSE IF (@Clubname = @mat_AgainstClubname)
	BEGIN
		SET @Clubname = @mat_AgainstClubname
		SET @Season = @mat_AgainstSeason
		SET @TeamName = @mat_AgainstTeamName
	END
	ELSE
	BEGIN
		SELECT CAST(1 AS BIT)
	END
    
    IF EXISTS
	--Alle spelers van het betreffende team
		(SELECT tmm_PersonID FROM dbo.tblTeamMembers
		WHERE tmm_Season = @Season 			
			-- Spelers uit het uit of thuis team
			AND tmm_Clubname+tmm_TeamName IN (@mat_HomeClubname+@mat_HomeTeamName, @mat_AgainstClubname+@mat_AgainstTeamName)
			-- Behalve spelers reeds geselecteerd
			AND tmm_PersonID NOT IN 				
				(SELECT mpl_PersonID FROM dbo.tblMatchPlayers 
				 WHERE mpl_HomeClubname = @mat_HomeClubname
					AND mpl_HomeSeason = @mat_HomeSeason
					AND mpl_HomeTeamName = @mat_HomeTeamName
					AND mpl_AgainstClubname = @mat_AgainstClubname
					AND mpl_AgainstSeason = @mat_AgainstSeason
					AND mpl_AgainstTeamName = @mat_AgainstTeamName)
			-- En alleen actieve spelers
			AND tmm_PersonID IN			
				(SELECT prs_ID FROM dbo.tblPersons
				 WHERE prs_Active = 1 AND prs_State = 'Speler')
			AND dbo.fnGetSuspensions(tmm_PersonID,@mat_Date,@Season) = 0)
	BEGIN
		SELECT CAST(1 AS BIT)
	END
	ELSE
	BEGIN
		SELECT CAST(0 AS BIT)
	END
	
	END TRY
	BEGIN CATCH
	    DECLARE @ErrorMessage NVARCHAR(4000);
	    DECLARE @ErrorSeverity INT;
	    DECLARE @ErrorState INT;
	    SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();
	    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);	    
	END CATCH
END
GO

DELETE FROM [tblUFGMenuActions] WHERE [uma_TableName] = 'tblMatches' AND [uma_SequenceNumber] = 1
GO
INSERT INTO [tblUFGMenuActions]
           ([uma_TableName]
           ,[uma_SequenceNumber]
           ,[uma_StoredProcedure]
           ,[uma_Icon]
           ,[uma_ShowTable]
           ,[uma_ShowConditionProcedure]
           ,[uma_ToggleVisible]
           ,[uma_VisibleInGrid]
           ,[uma_PanelTable]
           ,[uma_PanelNumber]
           ,[uma_HAlign]
           ,[uma_VAlign]
           ,[uma_ValidatesData] )          
     VALUES
           ('tblMatches'
           ,1
           ,'sptblMatchPlayers_AddTeamSel'
           ,'icon_user.gif'
           ,null
           ,'sptblMatchPlayers_AddTeamSelAct'
           ,1
           ,0
           ,'tblMatches'
           ,1
           ,'Right'
           ,'Top'
           ,1)
GO

INSERT INTO dbo.tblUFGMenuActionRoles
		(uar_TableName,
		 uar_SequenceNumber,
		 uar_RoleName)
	VALUES
		('tblMatches',
		 1,
		 'Wedstrijdsecretariaat Club')
GO

INSERT INTO dbo.tblUFGMenuActionTitles
		(uat_TableName,
		 uat_SequenceNumber,
		 uat_Language,
		 uat_Title,
		 uat_Tooltip,
		 uat_ShowConditionDescription,
		 uat_ConfirmationMessage)
	VALUES
		('tblMatches', 1, 'Nederlands', 'Alle leden invoegen', 'Alle teamleden invoegen', null, 'Alle teamleden invoegen?')
GO
